package com.hopool.shop.dao.product;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.hopool.shop.dao.BaseDao;
import com.hopool.shop.product.ProductType;

public class ProductTypeDao extends BaseDao{
	
	public void deleteAllNode(){
		getSession().createQuery("delete from ProductType").executeUpdate();
	}
	
	public List<ProductType> findFirstSubLevelNode(long parentId){
		String hql = "from ProductType pt where pt.parentType = ?";
		Query q = getSession().createQuery(hql);
		q.setLong(0, parentId);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public ProductType findNodeByName(String nodeName){
		return (ProductType) getSession()
			.createQuery("from ProductType pt where pt.name=?").setString(0, nodeName).uniqueResult();
	}
	
	public List<ProductType> retrievFullTree(long rootId){
		String hql = "select new ProductType" +
				" (node.id,node.name,node.parentType,node.left,node.right,node.depth,node.createDate)" +
				" from ProductType as node ,ProductType as parent" +
				" where node.left between parent.left and parent.right" +
				" and parent.id =?";
		Query q = getSession().createQuery(hql);
		q.setLong(0, rootId);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public List<ProductType> findAllLeafNodes(){
		String hql = "from ProductType pt where pt.right = pt.left +1";
		Query q = getSession().createQuery(hql);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public List<ProductType> retrievSinglePath(Long leafNodeId){
		String hql = "select new ProductType" +
				" (parent.id,parent.name,parent.parentType,parent.left,parent.right,parent.depth,parent.createDate)" +
				" from ProductType node ,ProductType parent" +
				" where node.left between parent.left and parent.right" +
				" and node.id = ? " +
				" order by parent.left";

		Query q = getSession().createQuery(hql);
		q.setLong(0, leafNodeId);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public List<ProductType> findDepthOfNodes(){
		String hql = "select new ProductType" +
				" (node.id,node.name,node.parentType,node.left,node.right,count(parent.name)-1,node.createDate)" +
				" from ProductType node ,ProductType parent" +
				" where node.left between parent.left and parent.right" +
				" group by node.name" +
				" order by node.left";
		Query q = getSession().createQuery(hql);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public List<ProductType> findDepthOfSubTree(Long startNodeId){
		String hql = "select new ProductType" +
				" (node.id,node.name,node.parentType,node.left,node.right,count(parent.name)-(sub_tree.depth +1),node.createDate)" +
				" from ProductType node,ProductType parent,ProductType sub_parent," +
					" ProductTypeTreeDepth as sub_tree" + 									//hql suselect
				" where node.left between parent.left and parent.right" +
				" and node.left between sub_parent.left and sub_parent.right" +
				" and sub_parent.name = sub_tree.name " +
				" and sub_tree.id=?" +
				" group by node.name" +
				" order by node.left";
		
		Query q = getSession().createQuery(hql);
		q.setLong(0, startNodeId);
		List<ProductType> pts = q.list();
		return pts;
	}
	
	public List<ProductType> findImmediateSubordinatesNode(Long startNodeId,Long depth){
		//TODO the native jdbc implement for the hql can not implements;
		return null;
	}
	
	public boolean isProductTypeNoData(){
		Long count = (Long) getSession().createQuery("select count(*) from ProductType pt").uniqueResult();
		if(count>0){
			return false;
		}else{
			return true;
		}
	}
	
	public void addNewNodeTheSame(ProductType newPt,Long brotherPtId){
		Session session = getSession();
		ProductType brotherProductType = 
			(ProductType) session.createQuery("from ProductType pt where pt.id=?")
			.setLong(0, brotherPtId).uniqueResult();
		if(brotherProductType==null){
			if(!isProductTypeNoData()){
				throw new RuntimeException("the ProductType is not Exist for id: "+brotherPtId+" is error");
			}
			newPt.setDepth(0L);
			newPt.setParentType(0L);
			newPt.setLeft(1L);
			newPt.setRight(2L);
		}else{
			Long myRight = brotherProductType.getRight();
			session.createQuery("update ProductType pt set pt.right = pt.right+2 where pt.right > ?")
					.setLong(0, myRight).executeUpdate();
			session.createQuery("update ProductType pt set pt.left = pt.left+2 where pt.left > ?")
					.setLong(0, myRight).executeUpdate();
			newPt.setDepth(brotherProductType.getDepth());
			newPt.setParentType(brotherProductType.getParentType());
			newPt.setLeft(myRight+1);
			newPt.setRight(myRight+2);
		}
		session.save(newPt);
	}
	
	public void addNewNodeTheChild(ProductType newPt,Long parentPtId){
		Session session = getSession();
		ProductType parentProductType = 
			(ProductType) session.createQuery("from ProductType pt where pt.id=?")
			.setLong(0, parentPtId).uniqueResult();
		if(parentProductType==null){
			if(!isProductTypeNoData()){
				throw new RuntimeException("the ProductType is not Exist for id: "+parentPtId+" is error");
			}
			newPt.setDepth(0L);
			newPt.setParentType(0L);
			newPt.setLeft(1L);
			newPt.setRight(2L);
		}else{
			Long myLeft = parentProductType.getLeft();
			session.createQuery("update ProductType pt set pt.right = pt.right+2 where pt.right > ?")
					.setLong(0, myLeft).executeUpdate();
			session.createQuery("update ProductType pt set pt.left = pt.left+2 where pt.left > ?")
					.setLong(0, myLeft).executeUpdate();
			newPt.setDepth(parentProductType.getDepth()+1);
			newPt.setParentType(parentProductType.getId());
			newPt.setLeft(myLeft+1);
			newPt.setRight(myLeft+2);
		}
		session.save(newPt);
		
	}
	
	public void deleteNode(Long deletePtId){
		Session session = getSession();
		ProductType pt = 
			(ProductType) session.createQuery("from ProductType pt where pt.id=?")
			.setLong(0, deletePtId).uniqueResult();
		Long myLeft = pt.getLeft();
		Long myRight = pt.getRight();
		Long myWidth = pt.getRight() - pt.getLeft() +1;
		
		session.createQuery("delete from ProductType pt where pt.left between ? and ?")
					.setLong(0, myLeft).setLong(1, myRight).executeUpdate();
		
		session.createQuery("update ProductType pt set pt.right = pt.right-? where pt.right > ?")
					.setLong(0, myWidth).setLong(1, myRight).executeUpdate();
		session.createQuery("update ProductType pt set pt.left = pt.left-? where pt.left > ?")
					.setLong(0, myWidth).setLong(1, myRight).executeUpdate();
		
	}
}
